﻿namespace System.Data.Entity.SqlServer
{
    using System.Collections.Generic;
    using System.Data.Entity.Core.Metadata.Edm;
    using System.Data.Entity.Infrastructure.Annotations;
    using System.Data.Entity.Migrations.Model;
    using System.Data.Entity.Utilities;
    using System.Linq;
    using System.Reflection;

    public class FullTextEnabledSqlGenerator : SqlServerMigrationSqlGenerator
    {
        #region Static members

        private const string _parameterName = "freetext";
        private const string _returnParameterName = "ReturnParam";

        private static string Escape(string s)
        {
            return (string)typeof(SqlServerMigrationSqlGenerator)
                .GetMethod("Escape", BindingFlags.Static | BindingFlags.NonPublic)
                .Invoke(null, new object[] { s });
        }

        #endregion

        protected override void Generate(CreateTableOperation createTableOperation)
        {
            if (createTableOperation == null)
                return;

            base.Generate(createTableOperation);

            // Process FullTextIndexedTable & FullTextIndex custom annotations

            FullTextIndexedTableAnnotation fullTextIndexedTableAnnotation =
                createTableOperation.GetTableAnnotation<FullTextIndexedTableAnnotation>(FullTextIndexedTableAnnotation.AnnotationName);

            if (fullTextIndexedTableAnnotation == null)
                return;

            IEnumerable<string> fullTextIndexAnnotations = createTableOperation.Columns
                .Select(column => column.Annotations)
                .Where(annotations => annotations
                    .Any(annotation => annotation.Key == FullTextIndexAnnotation.AnnotationName))
                    .SelectMany(annotations => annotations.Values)
                    .Select(annotationValues => annotationValues.NewValue as string)
                    .Where(newValue => newValue != null);

            if (fullTextIndexAnnotations.Count() == 0)
                return;

            var qualifiedFunctionName = Name(fullTextIndexedTableAnnotation
                .FullTextIndexedTable.QualifiedFunctionName);

            WriteCreateFunction(qualifiedFunctionName, createTableOperation, fullTextIndexAnnotations);
        }

        private void WriteCreateFunction(string name, CreateTableOperation createTableOperation, IEnumerable<string> fullTextIndexes)
        {
            var fullyQualifiedTableName = Name(createTableOperation.Name);

            using (var writer = Writer())
            {
                writer.WriteLine("IF OBJECT_ID (N'{0}', N'TF') IS NULL EXEC('", name);

                writer.Indent++;
                writer.WriteLine();

                writer.WriteLine("--Transact-SQL Multistatement Table-valued Function Syntax");
                writer.WriteLine();
                writer.WriteLine("CREATE FUNCTION {0}", name);

                writer.Indent++;
                writer.WriteLine("(");

                writer.Indent++;
                writer.Write("@{0} ", FullTextEnabledSqlGenerator._parameterName);
                writer.WriteLine(BuildColumnType(
                    new ColumnModel(PrimitiveTypeKind.String) { MaxLength = Int32.MaxValue }));

                writer.Indent--;

                writer.WriteLine(")");
                writer.Indent--;

                writer.Write("RETURNS @{0} TABLE (", FullTextEnabledSqlGenerator._returnParameterName);

                createTableOperation.Columns.Each(
                    (c, i) =>
                    {
                        writer.Write(Quote(c.Name));
                        writer.Write(" ");
                        writer.Write(BuildColumnType(c));

                        if (i < createTableOperation.Columns.Count - 1)
                            writer.Write(", ");
                    });

                writer.WriteLine(")");

                writer.WriteLine("AS");

                writer.Indent++;
                writer.WriteLine("BEGIN");

                writer.Indent++;
                writer.WriteLine("INSERT INTO @{0}", FullTextEnabledSqlGenerator._returnParameterName);

                writer.Write("SELECT ");

                createTableOperation.Columns.Each(
                    (c, i) =>
                    {
                        writer.Write(Quote(c.Name));

                        if (i < createTableOperation.Columns.Count - 1)
                            writer.Write(", ");
                    });

                writer.WriteLine(" FROM {0}", fullyQualifiedTableName);

                writer.Write("WHERE ");

                writer.Write(fullTextIndexes.Join(Quote));

                writer.WriteLine(" LIKE {0} + @{1} + {0}", FullTextEnabledSqlGenerator.Escape("'%'"), FullTextEnabledSqlGenerator._parameterName);
                writer.WriteLine("RETURN");
                writer.Indent--;

                writer.WriteLine("END");
                writer.Indent--;

                writer.Indent--;
                writer.Write("')");

                Statement(writer);
            }
        }
    }
}


/*
USE Kairos;
GO

--FULLTEXT CATALOG

DECLARE @fulltext_catalog_id int = 0;

IF NOT EXISTS(SELECT 1 FROM sys.fulltext_catalogs WHERE name='Kairos')
	CREATE FULLTEXT CATALOG Kairos WITH ACCENT_SENSITIVITY = OFF

SET @fulltext_catalog_id = (SELECT fulltext_catalog_id FROM sys.fulltext_catalogs WHERE name='Kairos')

--FULLTEXT INDEX ON Graphes

IF NOT EXISTS (SELECT 1 FROM sys.fulltext_index_columns, sys.fulltext_indexes
	LEFT JOIN sys.tables ON sys.fulltext_indexes.object_id = sys.tables.object_id
	LEFT JOIN sys.columns ON sys.columns.object_id = sys.tables.object_id
	WHERE 
		sys.fulltext_indexes.fulltext_catalog_id = @fulltext_catalog_id AND
		sys.fulltext_index_columns.object_id = sys.tables.object_id AND 
		sys.fulltext_index_columns.column_id = sys.columns.column_id AND
		sys.tables.name = 'Graphes' AND
		sys.columns.name = 'Titre')

	CREATE FULLTEXT INDEX ON Graphes
	(
		Titre								--Full-text index column name 
			--TYPE COLUMN FileExtension		--Name of column that contains file type information
			Language 1036					--1036 is the LCID for French
	)
	KEY INDEX [PK_dbo.Graphes] ON Kairos	--Unique index
	WITH CHANGE_TRACKING AUTO				--Population type;
	GO

--FULLTEXT CATALOG

DECLARE @fulltext_catalog_id int = 0;

IF NOT EXISTS(SELECT 1 FROM sys.fulltext_catalogs WHERE name='Kairos')
	CREATE FULLTEXT CATALOG Kairos WITH ACCENT_SENSITIVITY = OFF

SET @fulltext_catalog_id = (SELECT fulltext_catalog_id FROM sys.fulltext_catalogs WHERE name='Kairos')

--FULLTEXT INDEX ON Documents

IF NOT EXISTS (SELECT 1 FROM sys.fulltext_index_columns, sys.fulltext_indexes
	LEFT JOIN sys.tables ON sys.fulltext_indexes.object_id = sys.tables.object_id
	LEFT JOIN sys.columns ON sys.columns.object_id = sys.tables.object_id
	WHERE 
		sys.fulltext_indexes.fulltext_catalog_id = @fulltext_catalog_id AND
		sys.fulltext_index_columns.object_id = sys.tables.object_id AND 
		sys.fulltext_index_columns.column_id = sys.columns.column_id AND
		sys.tables.name = 'Documents' AND
		sys.columns.name IN ('Titre','Contenu'))

	CREATE FULLTEXT INDEX ON Documents
	(
		Titre								--Full-text index column name 
			--TYPE COLUMN FileExtension		--Name of column that contains file type information
			Language 1036,					--1036 is the LCID for French
		Contenu								--Full-text index column name 
			--TYPE COLUMN FileExtension		--Name of column that contains file type information
			Language 1036					--1036 is the LCID for French
	)
	KEY INDEX [PK_dbo.Documents] ON Kairos	--Unique index
	WITH CHANGE_TRACKING AUTO				--Population type;
	GO

--FREETEXT SearchGraphes FUNCTION

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'SearchGraphes')
	DROP FUNCTION SearchGraphes
	GO

CREATE FUNCTION SearchGraphes (@searchstring nvarchar(4000)) RETURNS TABLE
	RETURN
	SELECT Graphes.* FROM 
		FREETEXTTABLE ([dbo].[Graphes], [Titre], @searchstring) A
		INNER JOIN [dbo].[Graphes] ON [A].[KEY] = [dbo].[Graphes].[ID]
	GO

--FREETEXT SearchGraphes FUNCTION

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'SearchDocuments')
	DROP FUNCTION SearchDocuments
	GO

CREATE FUNCTION SearchDocuments (@searchstring nvarchar(4000)) RETURNS TABLE
	RETURN
	SELECT Documents.* FROM 
		FREETEXTTABLE ([dbo].[Documents], ([Titre],[Contenu]), @searchstring) A
		INNER JOIN [dbo].[Documents] ON [A].[KEY] = [dbo].[Documents].[ID]
	GO
*/